package com.team01.pokemon.mapper;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.team01.pokemon.entity.Pokemon;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 宝可梦基础信息表 Mapper 接口
 * </p>
 *
 * @author author
 * @since 2025-09-13
 */
public interface PokemonMapper extends BaseMapper<Pokemon> {

        @Select("select max(id) from pokemon")
        int selectMaxId();

        @Select("SELECT MAX(CAST(number AS UNSIGNED)) FROM pokemon")
        int selectMaxNumber();

        /*
         * @Select("select p.* , ts.type_name , ts.type_color color \n" +
         * "from pokemon p join pokemon_types pts join types ts\n" +
         * "on p.id = pts.pokemon_id and pts.type_id = ts.id " +
         * "${ew.customSqlSegment} and (ts.type_name=#{type1} or ts.type_name=#{type2})"
         * )
         * List<Pokemon> selectByType(@Param("type1") String
         * type1, @Param("type2")String type2, @Param("ew") LambdaQueryWrapper<Pokemon>
         * queryWrapper);
         */

        // 单属性查询
        @Select("SELECT DISTINCT p.*, ts.type_name, ts.type_color color " +
                        "FROM pokemon p " +
                        "JOIN pokemon_types pts ON p.id = pts.pokemon_id " +
                        "JOIN types ts ON pts.type_id = ts.id " +
                        "WHERE (#{category} IS NULL OR p.category = #{category}) " +
                        "AND CAST(p.number AS UNSIGNED) BETWEEN #{beginNum} AND #{endNum} " +
                        "AND ts.type_name = #{typeName}")
        List<Pokemon> selectBySingleType(@Param("typeName") String typeName,
                        @Param("category") String category, @Param("beginNum") int beginNum,
                        @Param("endNum") int endNum);

        // 双属性查询 - 查找同时拥有两种属性的宝可梦
        @Select("SELECT DISTINCT p.*, " +
                        "GROUP_CONCAT(DISTINCT ts.type_name ORDER BY ts.type_name) as type_names, " +
                        "GROUP_CONCAT(DISTINCT ts.type_color ORDER BY ts.type_name) as type_colors " +
                        "FROM pokemon p " +
                        "JOIN pokemon_types pts ON p.id = pts.pokemon_id " +
                        "JOIN types ts ON pts.type_id = ts.id " +
                        "WHERE (#{category} IS NULL OR p.category = #{category}) " +
                        "AND CAST(p.number AS UNSIGNED) BETWEEN #{beginNum} AND #{endNum} " +
                        "AND p.id IN (" +
                        "  SELECT p1.id FROM pokemon p1 " +
                        "  JOIN pokemon_types pts1 ON p1.id = pts1.pokemon_id " +
                        "  JOIN types ts1 ON pts1.type_id = ts1.id " +
                        "  WHERE ts1.type_name = #{type1} " +
                        "  AND CAST(p1.number AS UNSIGNED) BETWEEN #{beginNum} AND #{endNum} " +
                        "  AND p1.id IN (" +
                        "    SELECT p2.id FROM pokemon p2 " +
                        "    JOIN pokemon_types pts2 ON p2.id = pts2.pokemon_id " +
                        "    JOIN types ts2 ON pts2.type_id = ts2.id " +
                        "    WHERE ts2.type_name = #{type2}" +
                        "    AND CAST(p2.number AS UNSIGNED) BETWEEN #{beginNum} AND #{endNum} " +
                        "  )" +
                        ") " +
                        "GROUP BY p.id")
        List<Pokemon> selectByDualType(@Param("type1") String type1, @Param("type2") String type2,
                        @Param("category") String category, @Param("beginNum") int beginNum,
                        @Param("endNum") int endNum);

        // 列表查询————情况四2：根据类型名称查询宝可梦
        // @Select("select DISTINCT p.* from pokemon p join pokemon_types pt on p.id =
        // pt.pokemon_id join types t on pt.type_id = t.id where t.type_name =
        // #{typeName}")
        // List<Pokemon> selectByTypeName(String typeName);

        // 列表查询————情况五：根据类型名称、分类、编号范围查询宝可梦
        List<Pokemon> selectListByMany(@Param("types") String types,
                        @Param("category") String category,
                        @Param("beginNum") String beginNum,
                        @Param("endNum") String endNum);

}
